/**
 * 
 */
package com.pub.util;

import java.io.Closeable;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;

import org.apache.log4j.Logger;

import com.pub.db.DBConnectionManager;



/**
 * @author iling
 *
 */
public class DBUtils implements Closeable
{
	private static int I_UPDATE_LIMIT = 300;
	
	private static Logger logger ;
	private DBConnectionManager dbcm;
	
	static
	{
		logger 	= Logger.getLogger(DBUtils.class.getName());
	}
	
	public DBUtils()
	{
		dbcm = DBConnectionManager.getInstance(DBUtils.class.getName());
		
	}
	
	public DBUtils(int updateLimit)
	{
		I_UPDATE_LIMIT 	= updateLimit;
		
		dbcm 			= DBConnectionManager.getInstance(DBUtils.class.getName());
		
	}
	
	@SuppressWarnings("unchecked")
	public List<List<String>> getSource(String DBTag, String strColumns, String strTableName, String strWhere, int bufferSize)
	{
		logger.debug("getAllRowSource method start...");
		
		List<List<String>> alReturn;
		if(bufferSize < 1)
			alReturn = new ArrayList<List<String>>(824);
		else
			alReturn = new ArrayList<List<String>>(Math.round((float)bufferSize / (float)0.75));
		
		StringBuilder sbSql = new StringBuilder(50);
		
		ArrayList<String> alTpl = new ArrayList<String>(12);
		ArrayList<String> alCache;

		Connection conn 		= null;
		Statement sm 			= null;
		ResultSet rs 			= null;
		ResultSetMetaData rsmd	= null;
		
        sbSql.append("SELECT ");
        sbSql.append(strColumns);
        sbSql.append(" FROM ");
        sbSql.append(strTableName);
        if(strWhere != null)
        	sbSql.append(strWhere);

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY);

            rs 		= sm.executeQuery(sbSql.toString());
			rsmd 	= rs.getMetaData();
			
			int intColumnCount = rsmd.getColumnCount();	//-- column count
			int j;

			while (rs.next()){
            	alCache = (ArrayList<String>)alTpl.clone();
            	
            	for(j = 1 ; j <= intColumnCount ; j++){
            		if(rs.getString(j) == null && rsmd.getColumnType(j) == Types.TIMESTAMP){
						alCache.add("0000-00-00 00:00:00");
					}else if(rs.getString(j) == null && rsmd.getColumnType(j) == Types.DATE){
							alCache.add("0000-00-00");
					}else if(rs.getString(j) == null && rsmd.getColumnType(j) == Types.TIME){
						alCache.add("00:00:00");
					}else{
						alCache.add(rs.getString(j));
					}
				}
            	
            	alReturn.add(alCache);
           }
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if(rs != null){
	            try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
        }
		
		logger.debug("getAllRowSource return data size is " + alReturn.size());
		
		return alReturn;
	}
	
	@SuppressWarnings("unchecked")
	public Map<String, ArrayList<String>> getSource(String DBTag, String strColumns, String strTableName, String strWhere, ArrayList<String> alKeys, ArrayList<String> alColumns)
	{
		logger.debug("getAllRowSource method start...");
		
		Map<String, ArrayList<String>> hmReturn = new HashMap<String, ArrayList<String>>(824);
		StringBuilder sbSql = new StringBuilder(50);
		
		ArrayList<String> alTpl = new ArrayList<String>(12);
		ArrayList<String> alCache;
		
		Connection conn 		= null;
		Statement sm 			= null;
		ResultSet rs 			= null;
		ResultSetMetaData rsmd	= null;
		
		sbSql.append("SELECT ");
        sbSql.append(strColumns);
        sbSql.append(" FROM ");
        sbSql.append(strTableName);
        if(strWhere != null)
        	sbSql.append(strWhere);

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY);

			rs 		= sm.executeQuery(sbSql.toString());
			rsmd 	= rs.getMetaData();
			
			int intColumnCount = rsmd.getColumnCount();	//-- column count

			for(int j = 1 ; j <= intColumnCount ; j++){
				alColumns.add(rsmd.getColumnLabel(j));
			}
			
			int iKeySize = alKeys.size();
			int i,j;
			
            while (rs.next()){
            	alCache = (ArrayList<String>)alTpl.clone();
            	
				for(j = 1 ; j <= intColumnCount ; j++){
	          		if(rs.getString(j) == null && rsmd.getColumnType(j) == Types.TIMESTAMP){
						alCache.add("0000-00-00 00:00:00");
					}else if(rs.getString(j) == null && rsmd.getColumnType(j) == Types.DATE){
							alCache.add("0000-00-00");
					}else if(rs.getString(j) == null && rsmd.getColumnType(j) == Types.TIME){
						alCache.add("00:00:00");
					}else
						alCache.add(rs.getString(j));
				}

				sbSql.delete(0, sbSql.length());

				if(iKeySize > 1){
					for(i = 0; i < iKeySize; i++){
						if(sbSql.length() > 0)
							sbSql.append("_");
						sbSql.append(rs.getString(alKeys.get(i)));
					}
				}else{
					sbSql.append(rs.getString(alKeys.get(0)));
				}
				
                hmReturn.put(sbSql.toString(), alCache);
            }
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if(rs != null){
	            try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
        }
		
		logger.debug("getAllRowSource return data size is " + hmReturn.size());

		return hmReturn;
	}
	
	@SuppressWarnings("unchecked")
	public Map<Integer, Set<Integer>> getSource(String DBTag, String strColumns, String strTableName, String strWhere, String singleKey)
	{
		logger.debug("getAllRowSource method start...");
		
		Map<Integer, Set<Integer>> hmReturn = new HashMap<Integer, Set<Integer>>(824);
		StringBuilder sbSql = new StringBuilder(50);
		
		HashSet<Integer> setTpl = new HashSet<Integer>(82);
		Set<Integer> setCache;
		
		Connection conn 		= null;
		Statement sm 			= null;
		ResultSet rs 			= null;
		ResultSetMetaData rsmd	= null;
		
		sbSql.append("SELECT ");
        sbSql.append(strColumns);
        sbSql.append(" FROM ");
        sbSql.append(strTableName);
        if(strWhere != null)
        	sbSql.append(strWhere);

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY);

			rs 		= sm.executeQuery(sbSql.toString());
			rsmd 	= rs.getMetaData();
			
			int intColumnCount = rsmd.getColumnCount();	//-- column count

			int j;
			
            while (rs.next()){
            	if(hmReturn.containsKey(rs.getInt(singleKey))){
            		setCache = hmReturn.get(rs.getInt(singleKey));
	            	
					for(j = 1 ; j <= intColumnCount ; j++){
						if(!rsmd.getColumnLabel(j).equalsIgnoreCase(singleKey))
							setCache.add(rs.getInt(j));
					}
            	}else{
            		setCache = (HashSet<Integer>)setTpl.clone();
            		for(j = 1 ; j <= intColumnCount ; j++){
            			if(!rsmd.getColumnLabel(j).equalsIgnoreCase(singleKey))
            				setCache.add(rs.getInt(j));
					}
            		
            		hmReturn.put(rs.getInt(singleKey), setCache);
            	}
            }
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if(rs != null){
	            try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
        }
		
		logger.debug("getAllRowSource return data size is " + hmReturn.size());

		return hmReturn;
	}
	
	/*
	 * @param keyIndex > 0
	 */
	@SuppressWarnings("unchecked")
	public Map<Integer, Set<Integer>> getSourceByKeyIndex(String DBTag, String strColumns, String strTableName, String strWhere, int keyIndex)
	{
		logger.debug("getAllRowSource method start...");
		
		Map<Integer, Set<Integer>> hmReturn = new HashMap<Integer, Set<Integer>>(824);
		StringBuilder sbSql = new StringBuilder(50);
		
		HashSet<Integer> setTpl = new HashSet<Integer>(82);
		Set<Integer> setCache;
		
		Connection conn 		= null;
		Statement sm 			= null;
		ResultSet rs 			= null;
		ResultSetMetaData rsmd	= null;
		
		sbSql.append("SELECT ");
        sbSql.append(strColumns);
        sbSql.append(" FROM ");
        sbSql.append(strTableName);
        if(strWhere != null)
        	sbSql.append(strWhere);

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY);

			rs 		= sm.executeQuery(sbSql.toString());
			rsmd 	= rs.getMetaData();
			
			int intColumnCount = rsmd.getColumnCount();	//-- column count

			int j;
			
            while (rs.next()){
            	if(hmReturn.containsKey(rs.getInt(keyIndex))){
            		setCache = hmReturn.get(rs.getInt(keyIndex));
	            	
 					for(j = 1 ; j <= intColumnCount ; j++){
						if(j != keyIndex)
							setCache.add(rs.getInt(j));
					}
            	}else{
            		setCache = (HashSet<Integer>)setTpl.clone();
            		for(j = 1 ; j <= intColumnCount ; j++){
            			if(j != keyIndex)
            				setCache.add(rs.getInt(j));
					}
            		
            		hmReturn.put(rs.getInt(keyIndex), setCache);
            	}
            }
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if(rs != null){
	            try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
        }
		
		logger.debug("getAllRowSource return data size is " + hmReturn.size());

		return hmReturn;
	}
	
	@SuppressWarnings("unchecked")
	public Map<Integer, Map<Integer, Set<Integer>>> getSource(String DBTag, String firstColumn, String secondColumn, String thirdColumn, String strTableName, String strWhere)
	{
		logger.debug("getAllRowSource method start...");
		
		Set<Integer> setDataList;
		HashSet<Integer> setDataListTpl = new HashSet<Integer>();
		
		Map<Integer, Set<Integer>> mapCache;
		HashMap<Integer, Set<Integer>> mapCacheTpl = new HashMap<Integer, Set<Integer>>();
		
		Map<Integer, Map<Integer, Set<Integer>>> hmReturn = new HashMap<Integer, Map<Integer, Set<Integer>>>(824);
		
		StringBuilder sbSql = new StringBuilder(50);
		
		Connection conn 		= null;
		Statement sm 			= null;
		ResultSet rs 			= null;
		
		sbSql.append("SELECT ");
        sbSql.append(firstColumn);
        sbSql.append(",");
        sbSql.append(secondColumn);
        sbSql.append(",");
        sbSql.append(thirdColumn);
        sbSql.append(" FROM ");
        sbSql.append(strTableName);
        if(strWhere != null)
        	sbSql.append(strWhere);

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY);

			rs 		= sm.executeQuery(sbSql.toString());
			
			while (rs.next()){
            	if(hmReturn.containsKey(rs.getInt(firstColumn))){
               		mapCache = hmReturn.get(rs.getInt(firstColumn));
               		
               		if(mapCache.containsKey(rs.getInt(secondColumn))){
               			setDataList = mapCache.get(rs.getInt(secondColumn));
                		setDataList.add(rs.getInt(thirdColumn));
               		}else{
               			setDataList = (HashSet<Integer>)setDataListTpl.clone();
                		setDataList.add(rs.getInt(thirdColumn));
                		
               			mapCache.put(rs.getInt(secondColumn), setDataList);
               		}
            	}else{
             		setDataList = (HashSet<Integer>)setDataListTpl.clone();
            		setDataList.add(rs.getInt(thirdColumn));
            		
            		mapCache = (HashMap<Integer, Set<Integer>>)mapCacheTpl.clone();
            		mapCache.put(rs.getInt(secondColumn), setDataList);
            		
            		hmReturn.put(rs.getInt(firstColumn), mapCache);
            	}
            }
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if(rs != null){
	            try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
        }
		
		logger.debug("getAllRowSource return data size is " + hmReturn.size());

		return hmReturn;
	}
	
	@SuppressWarnings("unchecked")
	public Map<Integer, Map<Integer, Map<Integer, Set<Integer>>>> getSource(String DBTag, String firstColumn, String secondColumn, String thirdColumn, String fourColumn, String strTableName, String strWhere)
	{
		logger.debug("getAllRowSource method start...");
		
		Set<Integer> setDataList;
		HashSet<Integer> setDataListTpl = new HashSet<Integer>();
		
		Map<Integer, Set<Integer>> mapThirdCache;
		HashMap<Integer, Set<Integer>> mapThirdCacheTpl = new HashMap<Integer, Set<Integer>>();
		
		Map<Integer, Map<Integer, Set<Integer>>> mapSecondCache;
		HashMap<Integer, Map<Integer, Set<Integer>>> mapSecondCacheTpl = new HashMap<Integer, Map<Integer, Set<Integer>>>();
		
		Map<Integer, Map<Integer, Map<Integer, Set<Integer>>>> hmReturn = new HashMap<Integer, Map<Integer,Map<Integer, Set<Integer>>>>(824);
		
		StringBuilder sbSql = new StringBuilder(50);
		
		Connection conn 		= null;
		Statement sm 			= null;
		ResultSet rs 			= null;
		
		sbSql.append("SELECT ");
        sbSql.append(firstColumn);
        sbSql.append(",");
        sbSql.append(secondColumn);
        sbSql.append(",");
        sbSql.append(thirdColumn);
        sbSql.append(",");
        sbSql.append(fourColumn);
        sbSql.append(" FROM ");
        sbSql.append(strTableName);
        if(strWhere != null)
        	sbSql.append(strWhere);

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY);

			rs 		= sm.executeQuery(sbSql.toString());
			
			while (rs.next()){
            	if(hmReturn.containsKey(rs.getInt(firstColumn))){
            		mapSecondCache = hmReturn.get(rs.getInt(firstColumn));
               		
               		if(mapSecondCache.containsKey(rs.getInt(secondColumn))){
               			mapThirdCache = mapSecondCache.get(rs.getInt(secondColumn));
               			
               			if(mapThirdCache.containsKey(rs.getInt(thirdColumn))){
               				setDataList = mapThirdCache.get(rs.getInt(thirdColumn));
               				setDataList.add(rs.getInt(thirdColumn));
               			}else{
               				setDataList = (HashSet<Integer>)setDataListTpl.clone();
               				setDataList.add(rs.getInt(fourColumn));
               			}
               		}else{
               			setDataList = (HashSet<Integer>)setDataListTpl.clone();
                		setDataList.add(rs.getInt(fourColumn));
                		
                		mapThirdCache = (HashMap<Integer, Set<Integer>>)mapThirdCacheTpl.clone();
                		mapThirdCache.put(rs.getInt(thirdColumn), setDataList);
               			
               			mapSecondCache.put(rs.getInt(secondColumn), mapThirdCache);
               		}
            	}else{
             		setDataList = (HashSet<Integer>)setDataListTpl.clone();
            		setDataList.add(rs.getInt(fourColumn));
            		
            		mapThirdCache = (HashMap<Integer, Set<Integer>>)mapThirdCacheTpl.clone();
            		mapThirdCache.put(rs.getInt(thirdColumn), setDataList);
            		
            		mapSecondCache = (HashMap<Integer, Map<Integer, Set<Integer>>>)mapSecondCacheTpl.clone();
            		mapSecondCache.put(rs.getInt(secondColumn), mapThirdCache);
            		
            		hmReturn.put(rs.getInt(firstColumn), mapSecondCache);
            	}
            }
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if(rs != null){
	            try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
        }
		
		logger.debug("getAllRowSource return data size is " + hmReturn.size());

		return hmReturn;
	}
	
	public Map<String, Map<String, String>> getSourceMap(String DBTag, String strColumns, String strTableName, String strWhere, ArrayList<String> alKeys, ArrayList<String> alColumns)
	{
		logger.debug("getAllRowSource method start...");
		
		Map<String, Map<String, String>> hmReturn = new HashMap<String, Map<String, String>>(824);
		StringBuilder sbSql = new StringBuilder(50);
		
		Map<String, String> mapCache;
		
		Connection conn 		= null;
		Statement sm 			= null;
		ResultSet rs 			= null;
		ResultSetMetaData rsmd	= null;
		
		sbSql.append("SELECT ");
        sbSql.append(strColumns);
        sbSql.append(" FROM ");
        sbSql.append(strTableName);
        if(strWhere != null)
        	sbSql.append(strWhere);

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY);

			logger.info("sql ************************** = " + sbSql.toString());
			rs 		= sm.executeQuery(sbSql.toString());
			rsmd 	= rs.getMetaData();
			
			int intColumnCount = rsmd.getColumnCount();	//-- column count

			for(int j = 1 ; j <= intColumnCount ; j++){
				alColumns.add(rsmd.getColumnLabel(j));
			}
			
			int iKeySize = alKeys.size();
			int i,j;
			
            while (rs.next()){
            	mapCache = new HashMap<String, String>(12);
            	
				for(j = 1 ; j <= intColumnCount ; j++){
	          		if(rs.getString(j) == null && rsmd.getColumnType(j) == Types.TIMESTAMP){
	          			mapCache.put(rsmd.getColumnLabel(j), "0000-00-00 00:00:00");
					}else if(rs.getString(j) == null && rsmd.getColumnType(j) == Types.DATE){
						mapCache.put(rsmd.getColumnLabel(j), "0000-00-00");
					}else if(rs.getString(j) == null && rsmd.getColumnType(j) == Types.TIME){
						mapCache.put(rsmd.getColumnLabel(j), "00:00:00");
					}else
						mapCache.put(rsmd.getColumnLabel(j), rs.getString(j));
				}

				sbSql.delete(0, sbSql.length());

				if(iKeySize > 1){
					for(i = 0; i < iKeySize; i++){
						if(sbSql.length() > 0)
							sbSql.append("_");
						sbSql.append(rs.getString(alKeys.get(i)));
					}
				}else{
					sbSql.append(rs.getString(alKeys.get(0)));
				}
				
                hmReturn.put(sbSql.toString(), mapCache);
            }
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if(rs != null){
	            try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
        }
		
		logger.debug("getAllRowSource return data size is " + hmReturn.size());

		return hmReturn;
	}
	
	/* get first row data
	 * 
	 */
	public Map<String, String> getSource(String DBTag, String strColumns, String strTableName, String strWhere)
	{
		logger.debug("getOneRowSource method start...");
		
		Map<String, String> hmReturn = new HashMap<String, String>();
		StringBuilder sbSql = new StringBuilder(50);
		
		Connection conn 		= null;
		Statement sm 			= null;
		ResultSet rs 			= null;
		ResultSetMetaData rsmd	= null;
		
        sbSql.append("SELECT ");
        sbSql.append(strColumns);
        sbSql.append(" FROM ");
        sbSql.append(strTableName);
        if(strWhere != null)
        	sbSql.append(strWhere);

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY);
			rs 		= sm.executeQuery(sbSql.toString());
			
            if (rs.next()){
            	rsmd 	= rs.getMetaData();
    			
    			int intColumnCount = rsmd.getColumnCount();	//-- column count
    			int j;
    			
 				for(j = 1 ; j <= intColumnCount ; j++){
            		if(rs.getString(j) == null && rsmd.getColumnType(j) == Types.TIMESTAMP){
						hmReturn.put(rsmd.getColumnLabel(j), "0000-00-00 00:00:00");
					}else if(rs.getString(j) == null && rsmd.getColumnType(j) == Types.DATE){
						hmReturn.put(rsmd.getColumnLabel(j), "0000-00-00");
					}else if(rs.getString(j) == null && rsmd.getColumnType(j) == Types.TIME){
						hmReturn.put(rsmd.getColumnLabel(j), "00:00:00");
					}else{
						hmReturn.put(rsmd.getColumnLabel(j), rs.getString(j));
					}
				}
           }
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if(rs != null){
	            try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
        }
		
		logger.debug("getOneRowSource return data size is " + hmReturn.size());
		
		return hmReturn;
	}
	
	@SuppressWarnings("unchecked")
	public int getTarget(String DBTag, ArrayList<String> alColumns, String strTableName, String strWhere, ArrayList<String> alKeys, Map<String, ArrayList<String>> sourceData, Set<ArrayList<String>> returnDeleteList)
	{
		logger.debug("getTarget method start...");
		logger.debug("source data size : " + sourceData.size());
		
		StringBuilder sbSql = new StringBuilder(50);
		
		ArrayList<String> alTpl = new ArrayList<String>(12);
		ArrayList<String> alCache;
		
		int iKeySize = alKeys.size();
		int i,j;
		int iCount = 0;

		ArrayList<String> alKeyTpl = new ArrayList<String>(iKeySize);

		Connection conn 		= null;
		Statement sm 			= null;
		ResultSet rs 			= null;
		ResultSetMetaData rsmd	= null;

		sbSql.append("SELECT ");
		sbSql.append(alColumns.toString().replaceAll("\\[", "").replaceAll("\\]", ""));
       	sbSql.append(" FROM ");
       	sbSql.append(strTableName);
       	sbSql.append(strWhere);
       	
		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY);

			rs 		= sm.executeQuery(sbSql.toString());
			rsmd 	= rs.getMetaData();
			
			int intColumnCount = rsmd.getColumnCount();	//-- column count
		
	        while (rs.next()){
	        	iCount++;
	        	 
	        	alCache = (ArrayList<String>)alTpl.clone();
	        	
				for(j = 1 ; j <= intColumnCount ; j++){
					if(rs.getString(j) == null && rsmd.getColumnType(j) == Types.TIMESTAMP){
						alCache.add("0000-00-00 00:00:00");
					}else if(rs.getString(j) == null && rsmd.getColumnType(j) == Types.DATE){
						alCache.add("0000-00-00");
					}else if(rs.getString(j) == null && rsmd.getColumnType(j) == Types.TIME){
						alCache.add("00:00:00");
					}else{
						alCache.add(rs.getString(j));
					}
				}
	
				sbSql.delete(0, sbSql.length());

				if(iKeySize > 1){
					for(i = 0; i < iKeySize; i++){
						if(sbSql.length() > 0)
							sbSql.append("_");
						sbSql.append(rs.getString(alKeys.get(i)));
					}
				}else{
					sbSql.append(rs.getString(alKeys.get(0)));
				}
				
	            if(sourceData.containsKey(sbSql.toString())){
	            	if(SecurityUtils.getMD5(alCache.toString()).equals(SecurityUtils.getMD5(sourceData.get(sbSql.toString()).toString())))
	            		sourceData.remove(sbSql.toString());//--> 
	            }else{
	            	alCache = (ArrayList<String>)alKeyTpl.clone();
					for(i = 0; i < iKeySize; i++){
						alCache.add(rs.getString(alKeys.get(i)));
					}
	            	
					returnDeleteList.add(alCache);
	            }
	        }
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if(rs != null){
	            try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		logger.debug("Delete data size : " + returnDeleteList.size());
		logger.debug("getTarget method end. return target size : " + iCount);
		
		return iCount;
	}
	
	public int update(String DBTag, String sql)
	{
		int iUpdateCount = 0;
		
		Connection conn = null;
		Statement sm 	= null;

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();
			
			iUpdateCount += sm.executeUpdate(sql.toString());
		} catch (Exception e){
			logger.error("SQL Error : " + sql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		logger.debug("update method update data size : " + iUpdateCount);
		
		return iUpdateCount;
	}
	
	public int insert(String DBTag, String sql)
	{
		int id = 0;
		
		Connection conn = null;
		Statement sm 	= null;
		ResultSet rs	= null;
		
		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();
			
			sm.executeUpdate(sql.toString(), Statement.RETURN_GENERATED_KEYS);
			rs = sm.getGeneratedKeys();
			if(rs.next()){
				id = rs.getInt(1);
			}
		} catch (Exception e){
			logger.error("SQL Error : " + sql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if (rs != null){
	        	try{
	        		rs.close();
				} catch (Exception e){
				}
	        }
			
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		logger.debug("insert method return id value : " + id);
		
		return id;
	}
	
	public int update(String DBTag, String strTableName, Map<String, String> setData, String where, Set<Integer> updateIDList)
	{
		int iUpdateCount 	= 0;
		int iCounter		= 0;
		
		StringBuilder sbSqlSet 	= new StringBuilder(200);
		StringBuilder sbSqlID 	= new StringBuilder(1000);
		StringBuilder sbSql 	= new StringBuilder(1500);
		
		for(Entry<String, String> keyValue : setData.entrySet()){
			if(sbSqlSet.length() > 0){
				sbSqlSet.append(", ");
				sbSqlSet.append(keyValue.getKey());
				sbSqlSet.append(" = '");
				sbSqlSet.append(StringUtils.addcslashes(keyValue.getValue()));
				sbSqlSet.append("'");
			}else{
				sbSqlSet.append("UPDATE ");
				sbSqlSet.append(strTableName);
				sbSqlSet.append(" SET ");

				sbSqlSet.append(keyValue.getKey());
				sbSqlSet.append(" = '");
				sbSqlSet.append(StringUtils.addcslashes(keyValue.getValue()));
				sbSqlSet.append("'");
			}
		}
		
		sbSqlSet.append(where);
		
		Connection conn = null;
		Statement sm 	= null;

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();
			for(Integer updateID : updateIDList){
				if(sbSqlID.length() > 0)
					sbSqlID.append(",");
				sbSqlID.append(updateID);
				
				iCounter++;
				
				if(iCounter > I_UPDATE_LIMIT){
					sbSql.append(sbSqlSet);
					sbSql.append(sbSqlID);
					sbSql.append(")");
					
					iUpdateCount += sm.executeUpdate(sbSql.toString());
					
					sbSql.delete(0, sbSql.length());
					sbSqlID.delete(0, sbSqlID.length());
					
					iCounter = 0;
				}
			}
			
			if(sbSqlID.length() > 0){
				sbSql.append(sbSqlSet);
				sbSql.append(sbSqlID);
				sbSql.append(")");
				
				iUpdateCount += sm.executeUpdate(sbSql.toString());
				
				sbSql.delete(0, sbSql.length());
				sbSqlID.delete(0, sbSqlID.length());
				
				iCounter = 0;
			}
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		logger.debug("update method update data size : " + iUpdateCount);

		return iUpdateCount;
	}
	
	public int insert(String DBTag, String strTableName, ArrayList<String> alColumns, ArrayList<ArrayList<String>> alUpdateDBDataList)
	{
		logger.debug("insert method start...");
		
		StringBuilder sbSql = new StringBuilder(I_UPDATE_LIMIT * 6 + 500);
		StringBuilder sbSqlOnDuplicateKey = new StringBuilder(500);
		
		int i;
		int iCountCache 	= 1;
		int iUpdateCount 	= 0;
		int iColumnSize 	= alColumns.size();
		
		String strInsertColumns = alColumns.toString().replaceAll("\\[", "").replaceAll("\\]", "");
		
		sbSql.append("INSERT INTO ");
        sbSql.append(strTableName);
        sbSql.append(" (");
        sbSql.append(strInsertColumns);
        sbSql.append(") VALUES ");

        sbSqlOnDuplicateKey.append(" ON DUPLICATE KEY UPDATE ");
        for(i = 0; i < iColumnSize; i++){
            sbSqlOnDuplicateKey.append(alColumns.get(i));
            sbSqlOnDuplicateKey.append("=Values(");
            sbSqlOnDuplicateKey.append(alColumns.get(i));
            sbSqlOnDuplicateKey.append("),");
        }
 
   		if(sbSqlOnDuplicateKey.charAt(sbSqlOnDuplicateKey.length() - 1) == ',')
   			sbSqlOnDuplicateKey.deleteCharAt(sbSqlOnDuplicateKey.length() - 1);

   		Connection conn = null;
		Statement sm 	= null;

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();

            for(ArrayList<String> alData : alUpdateDBDataList){		
    			
    			sbSql.append("(");
    			
    			for(i = 0; i < iColumnSize; i++){
    				sbSql.append("'");
    				sbSql.append(StringUtils.addcslashes(alData.get(i)));//-->
        			sbSql.append("',");
    			}
    			
    	   		if(sbSql.charAt(sbSql.length() - 1) == ',')
    	 			sbSql.deleteCharAt(sbSql.length() - 1);
     
    			sbSql.append(")");
 
    			if((iCountCache % I_UPDATE_LIMIT) == 0){
	 				sbSql.append(sbSqlOnDuplicateKey.toString());
	 				
	 				logger.info("sbSql.toString() = " + sbSql.toString());
	 				//iUpdateCount += sm.executeUpdate(sbSql.toString());
	 				
					sbSql.delete(0, sbSql.length());
					
					sbSql.append("INSERT INTO ");
			        sbSql.append(strTableName);
		            sbSql.append(" (");
		            sbSql.append(strInsertColumns);
		            sbSql.append(") VALUES ");
	 			}else{
	 				sbSql.append(",");
	 			}
    			
    			iCountCache++;
            }
    		
    		if(sbSql.charAt(sbSql.length() - 1) == ','){
	 			sbSql.deleteCharAt(sbSql.length() - 1);
 				sbSql.append(sbSqlOnDuplicateKey.toString());
 				
 				logger.info("sbSql.toString() = " + sbSql.toString());
 				//iUpdateCount += sm.executeUpdate(sbSql.toString());
	 		}
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		logger.debug("insert method update data size : " + iUpdateCount);
		
		return iUpdateCount;
	}
	
	public int insertArray(String DBTag, String strTableName, ArrayList<String> alColumns, ArrayList<String[]> alUpdateDBDataList)
	{
		logger.debug("insert method start...");
		
		StringBuilder sbSql = new StringBuilder(I_UPDATE_LIMIT * 6 + 500);
		StringBuilder sbSqlOnDuplicateKey = new StringBuilder(500);
		
		int i;
		int iCountCache 	= 1;
		int iUpdateCount 	= 0;
		int iColumnSize 	= alColumns.size();
		
		String strInsertColumns = alColumns.toString().replaceAll("\\[", "").replaceAll("\\]", "");
		
		sbSql.append("INSERT INTO ");
        sbSql.append(strTableName);
        sbSql.append(" (");
        sbSql.append(strInsertColumns);
        sbSql.append(") VALUES ");

        sbSqlOnDuplicateKey.append(" ON DUPLICATE KEY UPDATE ");
        for(i = 0; i < iColumnSize; i++){
            sbSqlOnDuplicateKey.append(alColumns.get(i));
            sbSqlOnDuplicateKey.append("=Values(");
            sbSqlOnDuplicateKey.append(alColumns.get(i));
            sbSqlOnDuplicateKey.append("),");
        }
 
   		if(sbSqlOnDuplicateKey.charAt(sbSqlOnDuplicateKey.length() - 1) == ',')
   			sbSqlOnDuplicateKey.deleteCharAt(sbSqlOnDuplicateKey.length() - 1);

   		Connection conn = null;
		Statement sm 	= null;

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();

            for(String[] arrData : alUpdateDBDataList){		
    			
    			sbSql.append("(");
    			
    			for(i = 0; i < iColumnSize; i++){
    				sbSql.append("'");
    				sbSql.append(StringUtils.addcslashes(arrData[i]));//-->
        			sbSql.append("',");
    			}
    			
    	   		if(sbSql.charAt(sbSql.length() - 1) == ',')
    	 			sbSql.deleteCharAt(sbSql.length() - 1);
     
    			sbSql.append(")");
 
    			if((iCountCache % I_UPDATE_LIMIT) == 0){
	 				sbSql.append(sbSqlOnDuplicateKey.toString());
	 				iUpdateCount += sm.executeUpdate(sbSql.toString());
	 				
					sbSql.delete(0, sbSql.length());
					
					sbSql.append("INSERT INTO ");
			        sbSql.append(strTableName);
		            sbSql.append(" (");
		            sbSql.append(strInsertColumns);
		            sbSql.append(") VALUES ");
	 			}else{
	 				sbSql.append(",");
	 			}
    			
    			iCountCache++;
            }
    		
    		if(sbSql.charAt(sbSql.length() - 1) == ','){
	 			sbSql.deleteCharAt(sbSql.length() - 1);
 				sbSql.append(sbSqlOnDuplicateKey.toString());
 				iUpdateCount += sm.executeUpdate(sbSql.toString());
	 		}
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		logger.debug("insert method update data size : " + iUpdateCount);
		
		return iUpdateCount;
	}
	
	public int insertArray(String DBTag, String tableName,  String[] columns, Map<String, String> data)
	{
		logger.debug("insert method start...");
		
		StringBuilder sbSql 				= new StringBuilder(5000);
		StringBuilder sbSqlOnDuplicateKey 	= new StringBuilder(300);
		
		int i;
		int iUpdateCount 	= 0;
		
		sbSql.append("INSERT INTO ");
        sbSql.append(tableName);
        sbSql.append(" (");
        for(i = 0; i < columns.length; i++){
            if(i > 0)
            	sbSql.append(",");
        	sbSql.append(columns[i]);
        }
        
        sbSql.append(") VALUES ");

        sbSqlOnDuplicateKey.append(" ON DUPLICATE KEY UPDATE ");
        for(i = 0; i < columns.length; i++){
            sbSqlOnDuplicateKey.append(columns[i]);
            sbSqlOnDuplicateKey.append("=Values(");
            sbSqlOnDuplicateKey.append(columns[i]);
            sbSqlOnDuplicateKey.append("),");
        }
 
   		if(sbSqlOnDuplicateKey.charAt(sbSqlOnDuplicateKey.length() - 1) == ',')
   			sbSqlOnDuplicateKey.deleteCharAt(sbSqlOnDuplicateKey.length() - 1);

   		Connection conn = null;
		Statement sm 	= null;

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();

			sbSql.append("(");
			for(i = 0; i < columns.length; i++){
				if(i > 0)
					sbSql.append(",");
				
				sbSql.append("'");
				sbSql.append(StringUtils.addcslashes(data.get(columns[i])));//-->
    			sbSql.append("'");
			}
			sbSql.append(")");
			
			sbSql.append(sbSqlOnDuplicateKey.toString());
			
			logger.debug("insert SQL : " + sbSql.toString());
			iUpdateCount += sm.executeUpdate(sbSql.toString());
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		logger.debug("insert method update data size : " + iUpdateCount);
		
		return iUpdateCount;
	}
	
	public int insertArrayAppend(String DBTag, String tableName,  String[] columns, Map<String, String> data, Map<String, String> appendData)
	{
		logger.debug("insert method start...");
		
		StringBuilder sbSql 				= new StringBuilder(5000);
		StringBuilder sbSqlOnDuplicateKey 	= new StringBuilder(300);
		
		int i;
		int iUpdateCount 	= 0;
		
		sbSql.append("INSERT INTO ");
        sbSql.append(tableName);
        sbSql.append(" (");
        for(i = 0; i < columns.length; i++){
            if(i > 0)
            	sbSql.append(",");
        	sbSql.append(columns[i]);
        }
        
        if(appendData != null){
	        for(Entry<String, String> append : appendData.entrySet()){
            	sbSql.append(",");
	        	sbSql.append(append.getKey());
	        }
        }
        
        sbSql.append(") VALUES ");

        sbSqlOnDuplicateKey.append(" ON DUPLICATE KEY UPDATE ");
        for(i = 0; i < columns.length; i++){
        	if(i > 0)
        		sbSqlOnDuplicateKey.append(",");
        		
            sbSqlOnDuplicateKey.append(columns[i]);
            sbSqlOnDuplicateKey.append("=Values(");
            sbSqlOnDuplicateKey.append(columns[i]);
            sbSqlOnDuplicateKey.append(")");
        }
 
//        if(appendData != null){
//	        for(Entry<String, String> append : appendData.entrySet()){
//	        	sbSqlOnDuplicateKey.append(",");
//	        	
//	        	sbSqlOnDuplicateKey.append(append.getKey());
//	            sbSqlOnDuplicateKey.append("=Values(");
//	            sbSqlOnDuplicateKey.append(append.getKey());
//	            sbSqlOnDuplicateKey.append(")");
//	        }
//        }
 
   		if(sbSqlOnDuplicateKey.charAt(sbSqlOnDuplicateKey.length() - 1) == ',')
   			sbSqlOnDuplicateKey.deleteCharAt(sbSqlOnDuplicateKey.length() - 1);

   		Connection conn = null;
		Statement sm 	= null;

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();

			sbSql.append("(");
			for(i = 0; i < columns.length; i++){
				if(i > 0)
					sbSql.append(",");
				
				sbSql.append("'");
				sbSql.append(StringUtils.addcslashes(data.get(columns[i])));//-->
    			sbSql.append("'");
			}
			
			if(appendData != null){
		        for(Entry<String, String> append : appendData.entrySet()){
		        	sbSql.append(",");
					sbSql.append(StringUtils.addcslashes(append.getValue()));//-->
	    			sbSql.append("");
		        }
			}
			
			sbSql.append(")");
			
			sbSql.append(sbSqlOnDuplicateKey.toString());
			
			logger.debug("insert SQL : " + sbSql.toString());
			iUpdateCount += sm.executeUpdate(sbSql.toString());
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		logger.debug("insert method update data size : " + iUpdateCount);
		
		return iUpdateCount;
	}
	
	public int insert(String DBTag, String strTableName, ArrayList<String> alColumns, Map<String, ArrayList<String>> hmUpdateDBDataList)
	{
		logger.debug("insert method start...");
		
		StringBuilder sbSql = new StringBuilder(I_UPDATE_LIMIT * 6 + 500);
		StringBuilder sbSqlOnDuplicateKey = new StringBuilder(500);
		
		int i;
		int iCountCache 	= 1;
		int iUpdateCount 	= 0;
		int iColumnSize 	= alColumns.size();
		
		String strInsertColumns = alColumns.toString().replaceAll("\\[", "").replaceAll("\\]", "");
		
		ArrayList<String> 	alCache;
		
		sbSql.append("INSERT INTO ");
        sbSql.append(strTableName);
        sbSql.append(" (");
        sbSql.append(strInsertColumns);
        sbSql.append(") VALUES ");

        sbSqlOnDuplicateKey.append(" ON DUPLICATE KEY UPDATE ");
        for(i = 0; i < iColumnSize; i++){
            sbSqlOnDuplicateKey.append(alColumns.get(i));
            sbSqlOnDuplicateKey.append("=Values(");
            sbSqlOnDuplicateKey.append(alColumns.get(i));
            sbSqlOnDuplicateKey.append("),");
        }
 
   		if(sbSqlOnDuplicateKey.charAt(sbSqlOnDuplicateKey.length() - 1) == ',')
   			sbSqlOnDuplicateKey.deleteCharAt(sbSqlOnDuplicateKey.length() - 1);

   		Connection conn = null;
		Statement sm 	= null;

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();

            for(Entry<String, ArrayList<String>> entry : hmUpdateDBDataList.entrySet()){//key: key, value: data  		
    			alCache 	= entry.getValue();
    			
    			sbSql.append("(");
    			
    			for(i = 0; i < iColumnSize; i++){
    				sbSql.append("'");
    				sbSql.append(StringUtils.addcslashes(alCache.get(i)));//-->
        			sbSql.append("',");
    			}
    			
    	   		if(sbSql.charAt(sbSql.length() - 1) == ',')
    	 			sbSql.deleteCharAt(sbSql.length() - 1);
     
    			sbSql.append(")");
 
    			if((iCountCache % I_UPDATE_LIMIT) == 0){
	 				sbSql.append(sbSqlOnDuplicateKey.toString());
	 				iUpdateCount += sm.executeUpdate(sbSql.toString());
	 				
					sbSql.delete(0, sbSql.length());
					
					sbSql.append("INSERT INTO ");
			        sbSql.append(strTableName);
		            sbSql.append(" (");
		            sbSql.append(strInsertColumns);
		            sbSql.append(") VALUES ");
	 			}else{
	 				sbSql.append(",");
	 			}
    			
    			iCountCache++;
            }
    		
    		if(sbSql.charAt(sbSql.length() - 1) == ','){
	 			sbSql.deleteCharAt(sbSql.length() - 1);
 				sbSql.append(sbSqlOnDuplicateKey.toString());
 				iUpdateCount += sm.executeUpdate(sbSql.toString());
	 		}
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		logger.debug("insert method update data size : " + iUpdateCount);
		
		return iUpdateCount;
	}
	
	public int insertOneRow(String DBTag, String strTableName, ArrayList<String> alColumns, ArrayList<String> alUpdateDBData)
	{
		logger.debug("insert method start...");
		
		StringBuilder sbSql = new StringBuilder(I_UPDATE_LIMIT * 6 + 500);
		StringBuilder sbSqlOnDuplicateKey = new StringBuilder(500);
		
		int i;
		int iUpdateCount 	= 0;
		int iColumnSize 	= alColumns.size();
		
		String strInsertColumns = alColumns.toString().replaceAll("\\[", "").replaceAll("\\]", "");
		
		sbSql.append("INSERT INTO ");
        sbSql.append(strTableName);
        sbSql.append(" (");
        sbSql.append(strInsertColumns);
        sbSql.append(") VALUES ");

        sbSqlOnDuplicateKey.append(" ON DUPLICATE KEY UPDATE ");
        for(i = 0; i < iColumnSize; i++){
            sbSqlOnDuplicateKey.append(alColumns.get(i));
            sbSqlOnDuplicateKey.append("=Values(");
            sbSqlOnDuplicateKey.append(alColumns.get(i));
            sbSqlOnDuplicateKey.append("),");
        }
 
   		if(sbSqlOnDuplicateKey.charAt(sbSqlOnDuplicateKey.length() - 1) == ',')
   			sbSqlOnDuplicateKey.deleteCharAt(sbSqlOnDuplicateKey.length() - 1);

   		Connection conn = null;
		Statement sm 	= null;

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();

 			sbSql.append("(");
			
			for(i = 0; i < iColumnSize; i++){
				sbSql.append("'");
				sbSql.append(StringUtils.addcslashes(alUpdateDBData.get(i)));//-->
    			sbSql.append("',");
			}
			
	   		if(sbSql.charAt(sbSql.length() - 1) == ',')
	 			sbSql.deleteCharAt(sbSql.length() - 1);
 
			sbSql.append(")");
  			
			sbSql.append(sbSqlOnDuplicateKey.toString());
			iUpdateCount += sm.executeUpdate(sbSql.toString());
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		logger.debug("insert method update data size : " + iUpdateCount);
		
		return iUpdateCount;
	}
	
	public void delete(String DBTag, String strTableName, ArrayList<String> alKeys, Set<ArrayList<String>> deleteList)
	{
		int iKeySize 	= alKeys.size();
		int iDataSize 	= deleteList.size();
		
		int i;
		int iCountCache 	= 1;
		int iUpdateCount 	= 0;
		
		StringBuilder sbSql 	= new StringBuilder(500);
		StringBuilder sbAndSql	= new StringBuilder(50);
		
		sbSql.append("DELETE FROM ");
       	sbSql.append(strTableName);
       	sbSql.append(" WHERE ");
		
  		Connection conn = null;
		Statement sm 	= null;

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();

			for(ArrayList<String> alCache : deleteList){
				try{
					sbAndSql.delete(0, sbAndSql.length());
					
					for(i = 0; i < iKeySize; i++){
						if(sbAndSql.length() > 0)
							sbAndSql.append(" AND ");
							
						sbAndSql.append(alKeys.get(i));
						sbAndSql.append("='");
						sbAndSql.append(alCache.get(i));
						sbAndSql.append("'");
					}
					
					sbSql.append(sbAndSql.toString());
					
					if((iCountCache % I_UPDATE_LIMIT) == 0){
						iUpdateCount += sm.executeUpdate(sbSql.toString());
						
						sbSql.delete(0, sbSql.length());
						sbSql.append("DELETE FROM ");
				       	sbSql.append(strTableName);
				       	sbSql.append(" WHERE ");
					}else{
						if(iCountCache < iDataSize)
							sbSql.append(" OR ");
					}
				}catch(Exception e){
					logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
				}
				
				iCountCache++;
			}
			
			
			if(sbSql.charAt(sbSql.length() - 1) != ' '){
	 			try{
	 				iUpdateCount += sm.executeUpdate(sbSql.toString());
	 			}catch(Exception e){
	 				logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
				}
	 		}
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		logger.debug("Delete data total : " + iUpdateCount);
	}
	
	public int delete(String DBTag, String strTableName, String key, ArrayList<Integer> deleteList)
	{
		int iCountCache 	= 1;
		int iUpdateCount 	= 0;
		
		StringBuilder sbSql 	= new StringBuilder(500);
		
		sbSql.append("DELETE FROM ");
       	sbSql.append(strTableName);
       	sbSql.append(" WHERE ");
       	sbSql.append(key);
		sbSql.append(" IN (");

  		Connection conn = null;
		Statement sm 	= null;

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();

			for(Integer value : deleteList){
				try{
					sbSql.append(value);
					
					if((iCountCache % I_UPDATE_LIMIT) == 0){
						sbSql.append(")");
						
//						logger.error("SQL : " + sbSql.toString());
						
						iUpdateCount += sm.executeUpdate(sbSql.toString());
						
						sbSql.delete(0, sbSql.length());
						sbSql.append("DELETE FROM ");
				       	sbSql.append(strTableName);
				       	sbSql.append(" WHERE ");
				      	sbSql.append(key);
						sbSql.append(" IN (");
					}else{
						sbSql.append(",");
					}
				}catch(Exception e){
					logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
				}
				
				iCountCache++;
			}
			
			if(sbSql.charAt(sbSql.length() - 1) == ','){
	 			sbSql.deleteCharAt(sbSql.length() - 1);
	 			
	 			sbSql.append(")");
	 			
//	 			logger.error("SQL1111 : " + sbSql.toString());
				iUpdateCount += sm.executeUpdate(sbSql.toString());
 				
 				sbSql.delete(0, sbSql.length());
			}
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		logger.debug("Delete data total : " + iUpdateCount);
		
		return iUpdateCount;
	}
	
	public Map<Integer, Integer> getKeyValue(String DBTag, String strColumns, String strTableName, String strWhere)
	{
		logger.debug("getKeyValue method start...");
		
		Map<Integer, Integer> hmReturn = new HashMap<Integer, Integer>(824);
		StringBuilder sbSql = new StringBuilder(50);
		
		Connection conn 		= null;
		Statement sm 			= null;
		ResultSet rs 			= null;
		
		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY);

            sbSql.append("SELECT ");
            sbSql.append(strColumns);
            sbSql.append(" FROM ");
            sbSql.append(strTableName);
            sbSql.append(strWhere);
            
            rs 	= sm.executeQuery(sbSql.toString());
			
            while (rs.next()){
            	hmReturn.put(rs.getInt(1), rs.getInt(2));
           }
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if(rs != null){
	            try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
        }
		
		logger.debug("getKeyValue return data size is " + hmReturn.size());
		
		return hmReturn;
	}
	
	public Map<String, String> getKeyValueString(String DBTag, String strColumns, String strTableName, String strWhere)
	{
		logger.debug("getKeyValueString method start...");
		
		Map<String, String> hmReturn = new HashMap<String, String>(824);
		StringBuilder sbSql = new StringBuilder(50);
		
		Connection conn 		= null;
		Statement sm 			= null;
		ResultSet rs 			= null;
		
		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY);

            sbSql.append("SELECT ");
            sbSql.append(strColumns);
            sbSql.append(" FROM ");
            sbSql.append(strTableName);
            sbSql.append(strWhere);
            
            rs 	= sm.executeQuery(sbSql.toString());
			
            while (rs.next()){
            	hmReturn.put(rs.getString(1), rs.getString(2));
           }
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if(rs != null){
	            try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
        }
		
		logger.debug("getKeyValueString return data size is " + hmReturn.size());
		
		return hmReturn;
	}
	
	public Map<Integer, String> getKeyValueInt(String DBTag, String strColumns, String strTableName, String strWhere)
	{
		logger.debug("getKeyValueInt method start...");
		
		Map<Integer, String> hmReturn = new HashMap<Integer, String>(824);
		StringBuilder sbSql = new StringBuilder(50);
		
		Connection conn 		= null;
		Statement sm 			= null;
		ResultSet rs 			= null;
		
		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY);

            sbSql.append("SELECT ");
            sbSql.append(strColumns);
            sbSql.append(" FROM ");
            sbSql.append(strTableName);
            
            if(strWhere != null)
            	sbSql.append(strWhere);
            
            rs 	= sm.executeQuery(sbSql.toString());
			
            while (rs.next()){
            	hmReturn.put(rs.getInt(1), rs.getString(2));
           }
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if(rs != null){
	            try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
        }
		
		logger.debug("getKeyValueInt return data size is " + hmReturn.size());
		
		return hmReturn;
	}
	
	public Set<Integer> getKey(String DBTag, String strColumns, String strTableName, String strWhere)
	{
		logger.debug("getKey method start...");
		
		Set<Integer> setReturn 	= new HashSet<Integer>(824);
		StringBuilder sbSql 	= new StringBuilder(50);
		
		Connection conn 		= null;
		Statement sm 			= null;
		ResultSet rs 			= null;
		
		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY);

            sbSql.append("SELECT ");
            sbSql.append(strColumns);
            sbSql.append(" FROM ");
            sbSql.append(strTableName);
            
            if(strWhere != null)
            	sbSql.append(strWhere);
            
            rs 	= sm.executeQuery(sbSql.toString());
			
            while (rs.next()){
            	setReturn.add(rs.getInt(1));
           }
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if(rs != null){
	            try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
        }
		
		logger.debug("getKey return data size is " + setReturn.size());
		
		return setReturn;
	}

	public int getTotal(String DBTag, String sql)
	{
		int iReturn = 0;
		
		Connection conn = null;
		Statement sm 	= null;
		ResultSet rs 	= null;
		
		try{
			conn 	= dbcm.getConnection(DBTag);
			sm		= conn.createStatement();
			rs 		= sm.executeQuery(sql);
			
			if(rs.next()){
		    	iReturn = rs.getInt(1);
			}
		} catch (Exception e) {
			logger.error("error SQL : " + sql.toString() + ", Exception : " + e.getMessage());
		}finally{
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			
			if(sm != null){
				try {
					sm.close();
				} catch (SQLException e) {
				}
			}
			
			if(conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		return iReturn;
	}
	
	@Override
	public void close() throws IOException 
	{
		dbcm.release(DBUtils.class.getName());
	}
	

}
